package com.apes.fn.serveControl.serverParentPackage;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.jpa.Connector;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.lib.PubToolUtil;
import com.apestech.framework.db.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.stream.Collectors;

@Component
public class ServeControl extends ApesDao {
    @Autowired
    Connector connector;

    public JSONArray obtainMainList(JSONObject param) {
        StringBuffer sfPlatform = new StringBuffer();
        JSONArray pArray = param.getJSONArray("workPlatform");
        if(pArray != null && pArray.size() != 0){
            for(int i = 0;i<pArray.size();i++){
                if(i==0){
                    sfPlatform.append(" m.WORK_PLATFORM like '%" + pArray.get(i) + "%'");
                }else{
                    sfPlatform.append(" or m.WORK_PLATFORM like '%" + pArray.get(i) + "%'");
                }
            }
        };
        if(!"".equals(sfPlatform.toString().trim())){
            sfPlatform.insert(0," AND ( ");
            sfPlatform.append( " )");
        }

        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT m.*, ");
        sf.append(" CHANGE_TIME_SHOW( M.CONSUME_TIME*24*60 ) CONSUME_TIME_SHOW,  ");
        sf.append(" (CASE WHEN M.ESTIMATE_DATE IS NULL THEN 1 WHEN TO_DATE(TO_CHAR(M.ESTIMATE_DATE,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') - TO_DATE(TO_CHAR(M.PICK_CAR_TIME,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') < CONSUME_TIME THEN 0 ELSE 1 END) BEYONEFLAG ");
        sf.append(" FROM ( ");
        sf.append(" SELECT ");
        sf.append(" (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = A.DEPT_ID AND ROWNUM =1) DEPT_NAME,  ");
        sf.append(" (SELECT Y.NAME FROM  CAR_CONFIGURATION X,DC_VEHICLE_LABEL Y WHERE X.PZCX_ID = C.JEKUN_MODEL_CODE AND X.QCCP_ID = Y.ID AND ROWNUM =1) BRAND_NAME,  ");
        sf.append(" (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = A.DEPT_ID AND ROWNUM =1) WORK_DEPT_NAME,  ");
        sf.append(" (SELECT to_char(WMSYS.WM_CONCAT(Z.WORK_PLATFORM)) FROM SER_SERVICE_PLAN X, SER_SERVICE_PLAN_ITEM Y,SER_MAINTENANCE_ORDER Z ");
        sf.append(" WHERE X.ID = Y.SERVICE_PLAN_ID AND Y.ID = Z.SERVICE_PLAN_ITEM AND X.STATE IN ('qualityTest', 'confirm') AND X.BELONG_DEPT_ID = A.DEPT_ID AND X.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID) WORK_PLATFORM, ");
        sf.append(" E.ESTIMATE_DATE, ");
        sf.append(" (SELECT Y.NAME FROM FSM_FLOW_INST X,FSM_STATE Y WHERE X.BUSINESS_KEY = A.ID AND X.CURRENT_STATE = Y.ID AND X.FSM_FLOW_ID = '000161' AND X.FINISHED = 0 AND Y.STARTED = 0  AND ROWNUM =1) CURRENT_STATE, ");
        sf.append(" (SELECT Y.ID FROM FSM_FLOW_INST X,FSM_STATE Y WHERE X.BUSINESS_KEY = A.ID AND X.CURRENT_STATE = Y.ID AND X.FSM_FLOW_ID = '000161' AND X.FINISHED = 0 AND Y.STARTED = 0  AND ROWNUM =1) CURRENT_STATE_ID, ");
        sf.append(" C.CAR_LICENSE CAR_LICENSE,A.DEPT_ID,A.FN_CUSTOMER_EXPAND_ID,D.ID CURRENT_POINT_ID,D.NAME CURRENT_POINT,A.PICK_UP_CAR,A.ID VE_ID,E.CREATE_DATE PICK_CAR_TIME, ");
        sf.append(" (CASE WHEN E.FINISH_DATE IS NULL THEN SYSDATE - TO_DATE(TO_CHAR(E.CREATE_DATE,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') ELSE TO_DATE(TO_CHAR(E.FINISH_DATE,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') - TO_DATE(TO_CHAR(E.CREATE_DATE,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') END) CONSUME_TIME, " );
        sf.append(" (SELECT X.NAME FROM MAS_PARTY X, MAS_PARTY_ROLE Y WHERE X.ID = Y.PARTY_ID AND Y.ID = E.CREATOR_ID AND ROWNUM = 1) PICK_CAR_U ");
        sf.append(" FROM VEHICLE_INFO A LEFT JOIN FN_PICK_UP_CAR E ON A.PICK_UP_CAR = E.ID,FSM_FLOW_INST B,FN_CUSTOMER_EXPAND C,FSM_STATE D   ");
        sf.append(" WHERE A.ID = B.BUSINESS_KEY AND A.FN_CUSTOMER_EXPAND_ID = C.ID  AND B.CURRENT_STATE = D.ID AND A.STATE = 'start' AND D.FSM_FLOW_ID = '000124' ");
        sf.append(" AND D.STARTED = 0 AND D.FINISH = 0  ");
        sf.append(" @WHERE@ ");
        sf.append(" ) m WHERE 1=1 ");
        sf.append(sfPlatform);

        sf.append(" <if test=\"_labelList != null\"> AND (m.CURRENT_STATE_ID = #{_labelList}  or m.CURRENT_POINT_ID = #{_labelList} )  </if> AND 1=1 ");
        try{
            if(param.get("size")!=null && param.getInteger("size")>0) param.put("page",param.getInteger("page")+1);
            String sql="<rule from=\"A.VEHICLE_INFO;B.FSM_FLOW_INST;C.FN_CUSTOMER_EXPAND;D.FSM_STATE;E.FN_PICK_UP_CAR\" autoCompletion=\"1\" />"
                    + " <auth field=\"DEPT_ID\" type=\"pt_dept\" purpose=\"pp_search\" exp=\"DEPT_ID\" /> "
                    +sf.toString();
            SqlSession session=PubToolUtil.getSession(connector.getConnection());
            com.apestech.framework.json.JSONArray list=session.getForJSON().query(sql, new com.apestech.framework.json.JSONObject(param));
            Integer total = list.get(0).getJSONObject("SYS_PAGE").getInteger("Total");
            list.remove(0);
            return list.stream().map(item ->{
                JSONObject reJson = new JSONObject((com.apestech.framework.json.JSONObject)item);
                reJson.put("TOTAL",total);
                return reJson;
            }).collect(Collectors.toCollection(JSONArray::new));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public JSONArray obtainMainListHead(JSONObject param) {
        StringBuffer dept_condition = new StringBuffer();
        if(param.get("dept_id")!=null) {
            dept_condition.append(" AND A.DEPT_ID IN (");
            for(int i = 0;i<param.getJSONArray("dept_id").size();i++){
                if(i==0){
                    dept_condition.append(" '" +param.getJSONArray("dept_id").getString(i)+ "'");
                }else{
                    dept_condition.append(" ,'" +param.getJSONArray("dept_id").getString(i)+ "'");
                }
            }
            dept_condition.append(" )");
        }
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT K.NAME,K.ID,J.SUMCOUNT FROM FSM_STATE K LEFT JOIN ( ");
        sf.append(" SELECT C.ID,COUNT(*) SUMCOUNT  FROM VEHICLE_INFO A, FSM_FLOW_INST B ,FSM_STATE C ");
        sf.append(" WHERE A.ID = B.BUSINESS_KEY AND B.CURRENT_STATE = C.ID AND B.FINISHED = 0 AND A.STATE = 'start' AND B.FSM_FLOW_ID = '000124' AND C.STARTED = 0 AND C.FINISH = 0 ");
        sf.append(dept_condition);
        sf.append(" GROUP BY C.ID,C.NAME ");
        sf.append(" UNION ALL ");
        sf.append(" SELECT D.ID,COUNT(*) SUMCOUNT FROM FSM_STATE D,( ");
        sf.append(" SELECT (SELECT Y.ID FROM FSM_FLOW_INST X,FSM_STATE Y WHERE X.BUSINESS_KEY = A.ID AND X.CURRENT_STATE = Y.ID AND X.FSM_FLOW_ID = '000161' AND X.FINISHED = 0 AND Y.STARTED = 0  AND ROWNUM =1) CURRENT_STATE ");
        sf.append(" FROM VEHICLE_INFO A, FSM_FLOW_INST B ,FSM_STATE C ");
        sf.append(" WHERE A.ID = B.BUSINESS_KEY AND B.CURRENT_STATE = C.ID AND B.FINISHED = 0 AND A.STATE = 'start' AND B.FSM_FLOW_ID = '000124' ");
        sf.append(dept_condition);
        sf.append(" AND C.STARTED = 0 AND C.FINISH = 0 ");
        sf.append(" ) Z WHERE Z.CURRENT_STATE = D.ID GROUP BY D.ID,D.NAME ");
        sf.append(" ) J ON K.ID = J.ID WHERE K.STARTED= 0 AND K.FINISH = 0 AND K.FSM_FLOW_ID IN('000124','000161') ORDER BY K.ID ");
        return toJSONArray(find(sf.toString(), param));
    }



    public JSONArray obtainCarInfo(JSONObject param) {
        String fn_condition = " ";
        if(param.get("VE_ID") == null){
            throw new RuntimeException("缺少入参：业务单据编码");
        }else{
            fn_condition = " AND B.ID = '" + param.getString("VE_ID")  + "'";
        }
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT '' LOGO,A.CAR_LICENSE,A.REALNAME CUSTOMER_NAME,A.MOBILE, ");
        sf.append(" (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = B.DEPT_ID AND ROWNUM =1) DEPT_NAME, ");
        sf.append(" (SELECT E.NAME FROM MAS_CAR_CONFIGURATION D, MAS_CAR_LABEL E WHERE D.CAR_LABEL_ID = E.ID AND D.ID = A.JEKUN_MODEL_CODE AND ROWNUM =1) VEHICLELABEL ");
        sf.append(" FROM FN_CUSTOMER_EXPAND A,VEHICLE_INFO B  WHERE A.ID = B.FN_CUSTOMER_EXPAND_ID  ");
        sf.append(fn_condition);
        sf.append(" AND ROWNUM =1 ");
        return toJSONArray(find(sf.toString(), param));
    }

    public JSONArray obtainPickCarInfo(JSONObject param) {
        String fn_condition = " ";
        if(param.get("VE_ID") == null){
            throw new RuntimeException("缺少入参：业务单据编码");
        }else{
            fn_condition = " AND B.ID = '" + param.getString("VE_ID")  + "'";
        }
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT TO_CHAR(A.ESTIMATE_DATE,'yyyy-mm-dd hh24:mi:ss') ESTIMATE_DATE,A.CREATE_DATE PICK_DATE,A.MILEAGE,A.FUEL_METER,A.NOTE CUSTOMER_TIP,A.FAULT_DESCRIPTION,A.NOTE,TO_CHAR(A.FINISH_DATE,'yyyy-mm-dd hh24:mi:ss') FINISH_DATE,A.ADVANCE_REMARK, ");
        sf.append(" (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.FINISH_UID AND ROWNUM = 1) F_UID, ");
        sf.append(" (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = A.DEPT_ID AND ROWNUM =1) DEPT_NAME, ");
        sf.append(" (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = A.CREATOR_ID AND ROWNUM = 1) P_UID, ");
        sf.append(" (SELECT LISTAGG(Y.DESCRIPTION, ',') WITHIN GROUP(ORDER BY Z.PICK_UP_CAR_ID) TYPE FROM FN_PICK_UP_TYPE Z, MAS_ENUMERATION Y WHERE Z.TYPE = Y.ID AND Z.PICK_UP_CAR_ID = A.ID) PICK_TYPE ");
        sf.append(" FROM FN_PICK_UP_CAR A,VEHICLE_INFO B WHERE A.ID = B.PICK_UP_CAR ");
        sf.append(fn_condition);
        sf.append(" AND ROWNUM =1 ");
        return toJSONArray(find(sf.toString(), param));
    }

    public JSONArray obtainDetailList(JSONObject param) {
        String fn_condition = " ";
        if(param.get("VE_ID") == null){
            throw new RuntimeException("缺少入参：业务单据编码");
        }else{
            fn_condition = " AND A.ID = '" + param.getString("VE_ID")  + "'";
        }
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT A.ID VE_ID, B.ID PLAN_ID,TO_CHAR(B.CONFIRM_DATE,'yyyy-mm-dd hh24:mi:ss') CONFIRM_DATE, B.STATE PLAN_STATE,B.ESTIMATED_DELIVERY_DATE ES_DATE,C.FINISH_DATE PLAN_FINISH_DATE,TO_CHAR(C.QUALITY_TEST_DATE,'yyyy-mm-dd hh24:mi:ss') QUALITY_TEST_DATE, C.QUALITY_TEST_UID, C.QUALITY_FLAG,C.PFT_PRICE_UNIT,C.ASSIST_TYPE PLAN_ASSIST_TYPE,B.FINISH_WORK PLAN_FINISH_WORK,B.SALES_AMOUNT, ");
        sf.append(" TO_CHAR(B.RECEIVABLE_DATE,'yyyy-mm-dd hh24:mi:ss') RECEIVABLE_DATE, TO_CHAR(B.CREATE_DATE,'yyyy-mm-dd hh24:mi:ss') CREATE_DATE,C.QTY_RECEIVE,C.QTY_END, C.PRODUCT_ID,");
        sf.append(" (CASE B.STATE WHEN 'create' THEN '新增' WHEN 'toTechAudit' THEN '转技术审核' WHEN 'techAudit' THEN '技术审核完成' WHEN 'backTechAudit' THEN '技术审核退回' WHEN 'toPriceAudit' THEN '转价格审核' ");
        sf.append(" WHEN 'priceAudit' THEN '价格审核' WHEN 'backPriceAudit' THEN '价格审核退回' WHEN 'toInquiry' THEN '转询价' WHEN 'inquiryFinish' THEN '询价完成' WHEN 'backInquiry' THEN '询价退回' ");
        sf.append(" WHEN 'submission' THEN '提交' WHEN 'goBack' THEN '退回' WHEN 'confirm' THEN '确认' WHEN 'qualityTest' THEN '质检' WHEN 'termination' THEN '终止' WHEN 'receivables' THEN '收款完成'END) PLAN_STATE_NAME, ");
        //sf.append(" (SELECT Y.NAME FROM FSM_FLOW_INST X, FSM_STATE Y WHERE X.CURRENT_STATE = Y.ID AND B.ID = X.BUSINESS_KEY AND Y.STARTED = 0 AND Y.FINISH = 0 AND X.FINISHED = 0) POINT_NAME, ");
        sf.append(" (SELECT Z.NAME FROM MAS_PRODUCT Z WHERE Z.ID = C.PRODUCT_ID AND ROWNUM = 1) PRODUCT_NAME, ");
        sf.append(" (SELECT TO_CHAR(M.FINISH_DATE,'yyyy-mm-dd hh24:mi:ss') FINISH_DATE FROM SER_MAINTENANCE_ORDER M WHERE M.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) WORK_FINISH_DATE, ");
        sf.append(" (SELECT Q.PRODUCT_REQUIRE_ID FROM STOCK_PRODUCT_REQUIRE_ITEM Q WHERE Q.PRODUCT_REQUIRE_ID in (SELECT R.ID FROM STOCK_PRODUCT_REQUIRE R WHERE R.SOURCE = B.ID) AND Q.PRODUCT_ID = C.PRODUCT_ID AND ROWNUM = 1 ) REQUIRE_ID, ");
        sf.append(" (SELECT SUM(Q.QTY_ASSIGNED) FROM STOCK_PRODUCT_REQUIRE_ITEM Q WHERE Q.PRODUCT_REQUIRE_ID in (SELECT R.ID FROM STOCK_PRODUCT_REQUIRE R WHERE R.SOURCE = B.ID) AND Q.PRODUCT_ID = C.PRODUCT_ID ) QTY_ASSIGNED, ");
        sf.append(" (SELECT SUM(Q.QTY_REQUIRE) FROM STOCK_PRODUCT_REQUIRE_ITEM Q WHERE Q.PRODUCT_REQUIRE_ID in (SELECT R.ID FROM STOCK_PRODUCT_REQUIRE R WHERE R.SOURCE = B.ID) AND Q.PRODUCT_ID = C.PRODUCT_ID) QTY_REQUIRE, ");
        sf.append(" (SELECT SUM(Q.QTY_ARRIVE) FROM STOCK_PRODUCT_REQUIRE_ITEM Q WHERE Q.PRODUCT_REQUIRE_ID in (SELECT R.ID FROM STOCK_PRODUCT_REQUIRE R WHERE R.SOURCE = B.ID) AND Q.PRODUCT_ID = C.PRODUCT_ID) QTY_ARRIVE, ");
        sf.append(" (SELECT DECODE(Q.DEPT_ID,'1001','广州市车宝网络科技有限公司','1007','广州市集群车宝汽车服务连锁有限公司','') " );
        sf.append("  FROM STOCK_PRODUCT_REQUIRE_ITEM Q WHERE Q.PRODUCT_REQUIRE_ID = (SELECT R.ID FROM STOCK_PRODUCT_REQUIRE R WHERE R.SOURCE = B.ID AND ROWNUM = 1) AND Q.PRODUCT_ID = C.PRODUCT_ID) PLANT_NAME, ");
        sf.append(" (SELECT M.DISPATCHE_STATE FROM SER_MAINTENANCE_ORDER M WHERE M.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) DISPATCHE_STATE, ");
        sf.append(" (SELECT M.STATE FROM SER_MAINTENANCE_ORDER M WHERE M.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) MIAN_STATE, ");
        sf.append(" (SELECT M.WORK_STATE FROM SER_MAINTENANCE_ORDER M WHERE M.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) MAIN_WORK_STATE, ");
        sf.append(" (SELECT TO_CHAR(M.RANK_DATE,'yyyy-mm-dd hh24:mi:ss') RANK_DATE FROM SER_MAINTENANCE_ORDER M WHERE M.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) MAIN_RANK_DATE, ");
        sf.append(" (SELECT CHANGE_TIME_SHOW(M.WORKED_TIME) FROM SER_MAINTENANCE_ORDER M WHERE M.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) WORK_TIME, ");
        sf.append(" (SELECT U.NAME FROM MAS_PRODUCT_UOM U WHERE U.ID = (SELECT R.PRODUCT_UOM_ID FROM STOCK_PRODUCT_RECEIVE_ITEM R WHERE R.SERVICE_PLAN_ITEM_ID = C.ID AND ROWNUM = 1)) UOM_NAME,");
        sf.append(" (C.SALE_QTY-C.QTY_END-C.QTY_RECEIVE) USABLE_QTY,");
        sf.append(" (SELECT L.NAME FROM MAS_LOCATION L WHERE L.ID = C.LOCATION_ID) LOCATION_NAME,");
        sf.append(" (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = (SELECT G.CLERK_ID FROM RETAIL_ORDER G WHERE G.SOURCE= B.ID AND ROWNUM = 1 ) AND ROWNUM = 1) CLERK_ID, ");
        sf.append(" (C.SALE_QTY - C.QTY_END) QTY_SALE, ");
        sf.append(" CASE WHEN C.ASSIST_TYPE = 'ServicePlanItem.assistType.normal' THEN ");
        sf.append(" (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = B.BELONG_DEPT_ID AND ROWNUM = 1) ");
        sf.append(" WHEN C.ASSIST_TYPE = 'ServicePlanItem.assistType.inner' THEN ");
        sf.append(" (SELECT Z.NAME FROM SER_MAINTENANCE_ORDER Y, MAS_DEPT Z WHERE Y.INNER_DEPT_ID = Z.ID AND Y.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) ");
        sf.append(" WHEN C.ASSIST_TYPE = 'ServicePlanItem.assistType.outer' THEN  ");
        sf.append(" (SELECT S.NAME FROM SER_MAINTENANCE_ORDER X, ACC_CONTRACT Y, MAS_PARTY_ROLE Z,MAS_PARTY S WHERE X.SERVICE_PLAN_ITEM = C.ID AND Y.ID = X.CONTRACT_ID AND Y.PARTNER_ID = Z.ID AND Z.PARTY_ID = S.ID AND ROWNUM = 1) ");
        sf.append(" ELSE NULL END SERVE_SIDE_NAME, ");
        sf.append(" (SELECT O.NAME FROM MAS_PARTY O, MAS_PARTY_ROLE P WHERE O.ID = P.PARTY_ID AND P.ID = C.QUALITY_TEST_UID AND ROWNUM = 1) QUALITY_TEST_UNAME, ");
        sf.append(" (SELECT Z.PRODUCT_GROUP_ID FROM MAS_PRODUCT Z WHERE Z.ID = C.PRODUCT_ID AND ROWNUM = 1) PRODUCT_GROUP, ");
/*        sf.append(" (SELECT  ");
        sf.append(" CASE WHEN Z.STATE = 'create' THEN '新增' WHEN Z.STATE = 'create' THEN '新增' WHEN Z.STATE = 'inner' THEN '内协中' WHEN Z.STATE = 'outer' THEN '外协中' ");
        sf.append(" WHEN Z.STATE = 'termination' THEN '终止' WHEN Z.STATE = 'back' THEN '退回(外协重新派工)' WHEN Z.STATE = 'finish' THEN '完成' ELSE '' END ");
        sf.append("  FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) TEN_STATE, ");
        sf.append(" (SELECT  ");
        sf.append(" CASE WHEN Z.WORK_STATE = 'wait' THEN '待施工'  WHEN Z.WORK_STATE = 'pause' THEN '暂停中' ");
        sf.append("  WHEN Z.WORK_STATE = 'working' THEN '施工中'  WHEN Z.WORK_STATE = 'finish' THEN '完成' ELSE '' END ");
        sf.append("  FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) TEN_WORK_STATE, ");*/
        sf.append(" (SELECT TO_CHAR(Z.WORKER1_SHOW || ' ' || Z.WORKER2_SHOW || ' ' ||Z.WORKER3_SHOW || ' ' || Z.WORKER4_SHOW) FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = C.ID AND ROWNUM = 1) TEN_WORKER ");
        sf.append(" FROM VEHICLE_INFO A, SER_SERVICE_PLAN B, SER_SERVICE_PLAN_ITEM C ");
        sf.append(" WHERE A.DEPT_ID = B.BELONG_DEPT_ID AND A.FN_CUSTOMER_EXPAND_ID = B.FN_CUSTOMER_EXPAND_ID AND B.ID = C.SERVICE_PLAN_ID AND C.SERVICE_STATE = 1 AND C.TERMINATION_DATE IS NULL AND B.TERMINATION_DATE IS NULL ");
        sf.append(fn_condition);
        sf.append(" AND ROWNUM <= 2000 ORDER BY PLAN_ID ASC ");
        return toJSONArray(find(sf.toString(), param));
    }

    public JSONArray qryCarliscenceForControl(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        StringBuffer conditionSF = new StringBuffer();
        if(param.getString("conditon") != null){
            conditionSF.append(" AND A.CAR_LICENSE = '"+param.getString("conditon")+"' OR A.REALNAME = '"+param.getString("conditon")+"' OR A.MOBILE = '"+param.getString("conditon")+"' ");
        }
        sf.append(" SELECT G.* FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE FROM ( ");
        sf.append(" SELECT MAX(ROWNUM) OVER() TOTAL,m.* FROM ( ");
        sf.append(" SELECT DISTINCT A.CAR_LICENSE,A.REALNAME,A.MOBILE FROM FN_CUSTOMER_EXPAND A WHERE 1=1 ");
        sf.append(conditionSF);
        sf.append(" ) m WHERE 1=1  ");
        sf.append(" ) G WHERE ROWNUM <= DECODE(0, :PAGE, :SIZE, (:PAGE + 1) * :SIZE)) G WHERE RN > (:PAGE) * :SIZE ");
        return toJSONArray(find(sf.toString(), param));
    }

    /**
     * 用于外协总控
     * @return
     */
    public JSONArray obtainOutAssist() {
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT COUNT(B.ID) AMOUNT FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B ");
        sf.append(" WHERE A.ID = B.SERVICE_PLAN_ID AND B.SERVICE_STATE = 1 AND B.TERMINATION_DATE IS NULL AND B.ASSIST_TYPE = 'ServicePlanItem.assistType.outer' AND A.STATE = 'qualityTest' ");
        return toJSONArray(find(sf.toString()));
    }
    /**
     * 用于外协总控抽屉列表
     * @return
     */
    public JSONArray obtainOutAssistList(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT A.ID PLAN_ID,A.ID SERVICE_PLAN_ID, A.FN_CUSTOMER_EXPAND_ID, A.BELONG_DEPT_ID DEPT_ID, B.PRODUCT_ID,(B.SALE_QTY - B.QTY_END) QTY_SAL, B.PFT_PRICE_UNIT, ");
        sf.append(" (SELECT Z.CAR_LICENSE FROM FN_CUSTOMER_EXPAND Z WHERE Z.ID = A.FN_CUSTOMER_EXPAND_ID AND ROWNUM =1) CAR_LICENSE, ");
        sf.append(" (SELECT Z.MOBILE FROM FN_CUSTOMER_EXPAND Z WHERE Z.ID = A.FN_CUSTOMER_EXPAND_ID AND ROWNUM =1) MOBILE, ");
        sf.append(" (SELECT Z.NAME FROM MAS_DEPT Z WHERE Z.ID = A.BELONG_DEPT_ID AND ROWNUM =1) DEPE_NAME, ");
        sf.append(" (SELECT Z.NAME FROM MAS_PRODUCT Z WHERE Z.ID = B.PRODUCT_ID AND ROWNUM =1) PRODUCT_NAME, ");
        sf.append(" (SELECT CASE WHEN Z.STATE = 'finish' THEN '完成' ELSE '完成（异常处理）' END FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND Z.BUSINESS_TYPE = 'common' AND ROWNUM =1) STATE_NAME, ");
        sf.append(" (SELECT Y.NAME FROM SER_MAINTENANCE_ORDER Z,MAS_PARTY Y,MAS_PARTY_ROLE X WHERE Z.SERVICE_PLAN_ITEM = B.ID AND Z.DISPATCH_UID = X.ID AND X.PARTY_ID = Y.ID AND Z.BUSINESS_TYPE = 'common' AND ROWNUM =1) DISPACH_NAME, ");
        sf.append(" (SELECT Z.STATE FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND Z.BUSINESS_TYPE = 'out' AND Z.TERMINATION_DATE IS NULL AND ROWNUM =1) STATE, ");
        sf.append(" (SELECT TO_CHAR(Z.DISPATCH_DATE,'YYYY-MM-DD hh24:mi:ss') FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND Z.BUSINESS_TYPE = 'common' AND ROWNUM =1) DISPATCH_DATE ");
        sf.append(" FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B WHERE A.ID = B.SERVICE_PLAN_ID  ");
        sf.append(" AND B.SERVICE_STATE = 1 AND B.TERMINATION_DATE IS NULL AND B.ASSIST_TYPE = 'ServicePlanItem.assistType.outer' AND A.STATE = 'qualityTest' ");
        sf.append(" @WHERE@");
        sf.append(" GROUP BY A.ID, A.FN_CUSTOMER_EXPAND_ID, A.BELONG_DEPT_ID, B.PRODUCT_ID,B.SALE_QTY, B.PFT_PRICE_UNIT, B.QTY_END,B.ID ");

        try{
//            if(param.get("size")!=null && param.getInteger("size")>0) param.put("page",param.getInteger("page")+1);
            String sql="<rule from=\"A.SER_SERVICE_PLAN;B.SER_SERVICE_PLAN_ITEM\" autoCompletion=\"1\" />"+sf.toString();
            SqlSession session=PubToolUtil.getSession(connector.getConnection());
            com.apestech.framework.json.JSONArray list=session.getForJSON().query(sql, new com.apestech.framework.json.JSONObject(param));
            Integer total = list.get(0).getJSONObject("SYS_PAGE").getInteger("Total");
            list.remove(0);
            return list.stream().map(item ->{
                JSONObject reJson = new JSONObject((com.apestech.framework.json.JSONObject)item);
                reJson.put("TOTAL",total);
                return reJson;
            }).collect(Collectors.toCollection(JSONArray::new));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

}
